﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace festivalDatabase
{
    /// <summary>
    /// Allows for the addition, editing, and removal of sponsors with
    /// respect to the database. Allows for the addition and removal
    /// of sponsors with respect to a particular festival.
    /// </summary>
    public partial class editSponsors : userInterface
    {
        /// <summary>
        /// state variable for when modifying a sponsor
        /// </summary>
        string oldOrganization;

        /// <summary>
        /// Constructor: Initializes the editSponsors class variables.
        /// </summary>
        /// <param name="db">instance of the database</param>
        /// <param name="type">current festival type</param>
        public editSponsors(dbAccessor db, festivalTypes type)
        {
            InitializeComponent();
            setIcon();
            dbAccess = db;
            addSponsorRadio.Checked = true;
            moneyWatermark.Image = new Bitmap("moneyWatermark.tif");
            moneyWatermark.SizeMode = PictureBoxSizeMode.Zoom;
            currentFestivalType = type;
            updateFestivalLists();
        }

        /// <summary>
        /// Updates the list of festivals in the combo box and the list box.
        /// </summary>
        /// <param name="festival_type">String indicating the festival type.</param>
        public void updateFestivalLists()
        {
            festivalsComboBox.Items.Clear();
            festivalsSponsoredListBox.Items.Clear();

            if (orgIsInComboBox())
            {
                SQLiteCommand query = dbAccess.getCommand();
                query.CommandText = "SELECT name, id " +
                                    "FROM festival JOIN festival_has_sponsor " +
                                        "ON festival.id=festival_has_sponsor.festival_id " +
                                    "WHERE sponsor_org=@org;";
                SQLiteParameter param = new SQLiteParameter("@org", organizationComboBox.Text);
                query.Parameters.Add(param);

                DataTable dt = dbAccess.executeQuery(query);

                try
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        festivalsSponsoredListBox.Items.Add(row["name"].ToString() + ", " + row["id"].ToString());
                    }
                }
                catch
                {
                    removeFestivalButton.Enabled = false;
                }

                query = dbAccess.getCommand();
                query.CommandText = "SELECT name, id " +
                                    "FROM festival EXCEPT " +
                                    "SELECT name, id " +
                                    "FROM festival JOIN festival_has_sponsor " +
                                        "ON festival.id=festival_has_sponsor.festival_id " +
                                    "WHERE sponsor_org=@org;";
                query.Parameters.Add(param);

                dt = dbAccess.executeQuery(query);

                try
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        festivalsComboBox.Items.Add(row["name"].ToString() + ", " + row["id"].ToString());
                    }
                }
                catch
                {
                    addFestivalButton.Enabled = false;
                    amountMaskedTextBox.Enabled = false;
                }
            }
        }

        /// <summary>
        /// Method called when the radio for adding a sponsor changes state.
        /// Resets all fields and enables/disables components.
        /// </summary>
        /// <param name="sender">the object detecting the change in state</param>
        /// <param name="e">event argument</param>
        private void addSponsorRadio_CheckedChanged(object sender, EventArgs e)
        {
            if (addSponsorRadio.Checked)
            {
                organizationComboBox.Visible = false;
                organizationTextBox.Visible = true;
                contactTextBox.Enabled = true;
                contactTextBox.Text = "";
                phoneMaskedTextBox.Enabled = true;
                addFestivalButton.Enabled = false;
                removeFestivalButton.Enabled = false;
                amountMaskedTextBox.Enabled = false;
                festivalsComboBox.Enabled = false;
                festivalsComboBox.Text = "";
                phoneMaskedTextBox.Text = "";
                organizationComboBox.Text = "";
                sponsorSubmitButton.Text = "Add";
                updateFestivalLists();
            }
        }

        /// <summary>
        /// Method called when the radio for editing a sponsor changes state.
        /// Resets all fields and enables/disables components.
        /// </summary>
        /// <param name="sender">the object detecting the change in state</param>
        /// <param name="e">event argument</param>
        private void editSponsorRadio_CheckedChanged(object sender, EventArgs e)
        {
            if (editSponsorRadio.Checked)
            {
                organizationTextBox.Visible = false;
                organizationComboBox.Visible = true;
                contactTextBox.Enabled = true;
                addFestivalButton.Enabled = false;
                amountMaskedTextBox.Enabled = false;
                festivalsComboBox.Enabled = false;
                organizationComboBox.Text = "";
                contactTextBox.Text = "";
                phoneMaskedTextBox.Enabled = true;
                phoneMaskedTextBox.Text = "";
                sponsorSubmitButton.Text = "Edit";
                updateOrgComboBox();
                updateFestivalLists();
            }
        }

        /// <summary>
        /// Method called when the radio for deleting a sponsor changes state.
        /// Resets all fields and enables/disables components.
        /// </summary>
        /// <param name="sender">the object detecting the change in state</param>
        /// <param name="e">event argument</param>
        private void deleteSponsorRadio_CheckedChanged(object sender, EventArgs e)
        {
            if (deleteSponsorRadio.Checked)
            {
                organizationTextBox.Visible = false;
                organizationComboBox.Visible = true;
                contactTextBox.Enabled = false;
                contactTextBox.Text = "";
                phoneMaskedTextBox.Enabled = false;
                addFestivalButton.Enabled = false;
                removeFestivalButton.Enabled = false;
                amountMaskedTextBox.Enabled = false;
                festivalsComboBox.Enabled = false;
                festivalsComboBox.Text = "";
                phoneMaskedTextBox.Text = "";
                organizationComboBox.Text = "";
                sponsorSubmitButton.Text = "Delete";
                updateOrgComboBox();
                updateFestivalLists();
            }
        }

        /// <summary>
        /// Populates the combo box containing organizations the current sponsor
        /// is not yet sponsoring.
        /// </summary>
        private void updateOrgComboBox()
        {
            organizationComboBox.Items.Clear();

            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT organization " +
                                "FROM sponsor;";
            DataTable dt = dbAccess.executeQuery(query);

            foreach (DataRow row in dt.Rows)
                organizationComboBox.Items.Add(row["organization"].ToString());
        }

        /// <summary>
        /// Removes the indicated amount or the balance of a given sponsor's
        /// funds allocated to a particular festival.
        /// </summary>
        /// <param name="sender">the button calling this method</param>
        /// <param name="e">an event argument</param>
        private void removeFestivalButton_Click(object sender, EventArgs e)
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE from festival_has_sponsor " +
                                   "WHERE festival_id=@festID AND sponsor_org=@org;";
            SQLiteParameter param = new SQLiteParameter("@festID", getFestivalNumber(festivalsSponsoredListBox.SelectedItem.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@org", organizationComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            dbAccess.executeNonQuery(nonQuery);
            updateFestivalLists();
        }


        /// <summary>
        /// Called when submit button is clicked. Determines the current state
        /// of the radio buttons and calls a method to add, edit, or delete
        /// a given sponsor.
        /// </summary>
        /// <param name="sender">sender</param>
        /// <param name="e">eventarg</param>
        private void sponsorSubmitButton_Click(object sender, EventArgs e)
        {
            errorProvider.Clear();
            if (addSponsorRadio.Checked)
                addSponsor();
            else if (editSponsorRadio.Checked)
                editSponsor();
            else
                deleteSponsor();
        }

        /// <summary>
        /// Adds a sponsor to the database after validating input.
        /// </summary>
        private void addSponsor()
        {
            if (organizationTextBox.Text != "")
            {
                if (orgIsInComboBox() == false)
                {
                    if (validNumLength(phoneMaskedTextBox.Text, 10, true))
                    {
                        SQLiteCommand nonQuery = dbAccess.getCommand();
                        nonQuery.CommandText = "INSERT INTO sponsor " +
                                               "VALUES (@Org, @Contact, @Phone);";
                        SQLiteParameter param = new SQLiteParameter("@Org", organizationTextBox.Text.ToString());
                        nonQuery.Parameters.Add(param);
                        param = new SQLiteParameter("@Contact", contactTextBox.Text.ToString());
                        nonQuery.Parameters.Add(param);
                        param = new SQLiteParameter("@Phone", phoneMaskedTextBox.Text.ToString());
                        nonQuery.Parameters.Add(param);
                        dbAccess.executeNonQuery(nonQuery);
                        MessageBox.Show("Organization created successfully.");
                        organizationTextBox.Text = "";
                        phoneMaskedTextBox.Text = "";
                        contactTextBox.Text = "";
                    }
                    else
                        errorProvider.SetError(phoneMaskedTextBox, "Invalid phone number.");
                }
                else
                    errorProvider.SetError(organizationTextBox, "Organization already exists. Please enter a unique organization name or edit the desired organization.");
            }
            else
                errorProvider.SetError(organizationTextBox, "Please enter the organization's name.");
        }

        /// <summary>
        /// Edits a given sponsor after validating input.
        /// </summary>
        private void editSponsor()
        {
            if (orgIsInComboBox() == false || organizationComboBox.Text.ToString() == oldOrganization)
            {
                SQLiteCommand nonQuery = dbAccess.getCommand();
                nonQuery.CommandText = "UPDATE sponsor SET organization=@org, contact_name=@contact, telephone_number=@phone " +
                                       "WHERE organization=@oldOrg;";
                SQLiteParameter param = new SQLiteParameter("@org", organizationComboBox.Text.ToString());
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@contact", contactTextBox.Text.ToString());
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@phone", phoneMaskedTextBox.Text.ToString());
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@oldOrg", oldOrganization);
                nonQuery.Parameters.Add(param);
                dbAccess.executeNonQuery(nonQuery);

                nonQuery.CommandText = "UPDATE festival_has_sponsor SET sponsor_org=@org " +
                                       "WHERE sponsor_org=@oldOrg;";
                param = new SQLiteParameter("@org", organizationComboBox.Text.ToString());
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@oldOrg", oldOrganization);
                nonQuery.Parameters.Add(param);
                dbAccess.executeNonQuery(nonQuery);

                MessageBox.Show("Sponsor updated");
                updateOrgComboBox();
            }
            else
            {
                MessageBox.Show("Please enter a unique sponsor organization");
            }
        }

        /// <summary>
        /// Deletes a given sponsor.
        /// </summary>
        private void deleteSponsor()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM sponsor WHERE organization=@org";
            SQLiteParameter param = new SQLiteParameter("@org", organizationComboBox.Text);
            nonQuery.Parameters.Add(param);
            dbAccess.executeNonQuery(nonQuery);
            organizationComboBox.Text = "";
            phoneMaskedTextBox.Clear();
            contactTextBox.Text = "";
            updateFestivalLists();
            updateOrgComboBox();

            nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM festival_has_sponsor WHERE sponsor_org=@org";
            nonQuery.Parameters.Add(param);
            dbAccess.executeNonQuery(nonQuery);
        }

        /// <summary>
        /// Adds a festival to the list of festivals a sponsor is sponsoring after
        /// validating input.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void addFestivalButton_Click(object sender, EventArgs e)
        {
            errorProvider.Clear();
            if (festivalsComboBox.Text != "" && festivalIsInComboBox())
            {
                if (orgIsInComboBox())
                {
                    if (amountMaskedTextBox.Text.Length > 1)
                    {
                        SQLiteCommand nonQuery = dbAccess.getCommand();
                        nonQuery.CommandText = "INSERT INTO festival_has_sponsor " +
                                               "VALUES (@Id, @Org, @Amt);";
                        SQLiteParameter param = new SQLiteParameter("@Id", getFestivalNumber(festivalsComboBox.Text.ToString()));
                        nonQuery.Parameters.Add(param);
                        param = new SQLiteParameter("@Org", organizationComboBox.Text.ToString());
                        nonQuery.Parameters.Add(param);
                        param = new SQLiteParameter("@Amt", getDonationAmt(amountMaskedTextBox.Text.ToString()));
                        nonQuery.Parameters.Add(param);
                        dbAccess.executeNonQuery(nonQuery);
                        updateFestivalLists();
                        festivalsComboBox.Text = "";
                    }
                    else
                        errorProvider.SetError(amountMaskedTextBox, "Please indicate the amount donated.");
                }
                else
                    errorProvider.SetError(organizationComboBox, "Please select a valid organization. If none exist, add one first.");
            }
            else
                errorProvider.SetError(festivalsComboBox, "Festival not found; please select a festival from the dropdown.");
        }

        /// <summary>
        /// Converts a string containing numbers into an in-order int.
        /// </summary>
        /// <param name="amt">a string containing numbers</param>
        /// <returns>an int</returns>
        private int getDonationAmt(string amt)
        {
            char[] amount = amt.ToCharArray();
            int index = 0;

            for (int i=0; i<amount.Length; i++)
            {
                char c = amount[i];
                if(c == '1' || c == '2' || c == '3' || c == '4' || c == '5' || c == '6' || c == '7' || c == '8' || c == '9')
                    c = amount[index++];
            }

            try
            {
                char[] result = new char[index];

                for (int i = 0; i < index; i++)
                {
                    result[i] = amount[amount.Length-index];
                }

                string res = new string(result);

                return Int32.Parse(res);
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// Determines whether the festival in the combo box is valid (since users can just
        /// type in custom input which would crash the program.
        /// </summary>
        /// <returns>boolean indicating whether the festival is valid</returns>
        private bool festivalIsInComboBox()
        {
            foreach (DataRow row in festivals.Rows)
            {
                if(row["id"].ToString() == getFestivalNumber(festivalsComboBox.Text.ToString()))
                    return true;
            }

            return false;
        }

        /// <summary>
        /// Determines whether the organization in the combo box is valid (since users can just
        /// type in custom input which would crash the program.
        /// </summary>
        /// <returns>boolean indicating whether the org is valid</returns>
        private bool orgIsInComboBox()
        {
            if (organizationComboBox.Items.Count == 0)
                return false;

            foreach (string s in organizationComboBox.Items)
            {
                if (s == organizationComboBox.Text.ToString())
                    return true;
            }

            return false;
        }

        /// <summary>
        /// Method called when the selection is changed in the festival list box.
        /// If there are festivals sponsored, it enables the removal buttons.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void festivalsSponsoredListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (festivalsSponsoredListBox.Items.Count > 0)
            {
                removeFestivalButton.Enabled = true;
            }
        }

        /// <summary>
        /// Method called when the selection in the festival combo box is changed.
        /// Enables the buttons for adding a festival and amount sponsored.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void festivalsComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            addFestivalButton.Enabled = true;
            amountMaskedTextBox.Enabled = true;
        }

        /// <summary>
        /// Method called when organization selected in combo box for editing or deleting
        /// is changed. Populates the text fields with the info about the org.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void organizationComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            addFestivalButton.Enabled = true;
            festivalsComboBox.Enabled = true;
            amountMaskedTextBox.Enabled = true;
            oldOrganization = organizationComboBox.Text;

            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT * " +
                                "FROM sponsor " +
                                "WHERE organization = @Org;";
            SQLiteParameter param = new SQLiteParameter("@Org", organizationComboBox.Text.ToString());
            query.Parameters.Add(param);
            DataTable dt = dbAccess.executeQuery(query);
            updateFestivalLists();

            try
            {
                contactTextBox.Text = dt.Rows[0]["contact_name"].ToString();
                phoneMaskedTextBox.Text = dt.Rows[0]["telephone_number"].ToString();
            }
            catch
            {
                // oops, it failed; do nothin'
            }
        }
    }
}
